package com.sansi.pencilbook.mapper;

import com.sansi.pencilbook.entity.Book;
import com.sansi.pencilbook.entity.UseBook;
import org.apache.ibatis.annotations.*;

import java.sql.SQLException;
import java.util.List;

/**
 * 功能说明:书籍的增删改查
 *
 * @author lsy13
 */
@Mapper
public interface UseBookMapper {

    @Select("select book_isbn, category.category_name, book_img, book_name, book_publishers, book_price, " +
            "book_author, book_introduction, book_time from book left join category on category.category_id = book.category_id")
    public List<UseBook> findAll() throws SQLException;

    @Select("select book_isbn, category.category_name, book_img, book_name, book_publishers, book_price, " +
            "book_author, book_introduction, book_time from book left join category on category.category_id = book.category_id " +
            "where book_name like concat('%',#{name},'%')")
    public List<UseBook> findByName(String name) throws SQLException;

    @Select("select book_isbn, category.category_name, book_img, book_name, book_publishers, book_price, " +
            "book_author, book_introduction, book_time from book left join category on category.category_id = book.category_id " +
            "where book_publishers like concat('%',#{publisher},'%')")
    public List<UseBook> findByPublisher(String publisher) throws SQLException;

    @Select("select book_isbn, category.category_name, book_img, book_name, book_publishers, book_price, " +
            "book_author, book_introduction, book_time from book left join category on category.category_id = book.category_id " +
            "where book_author like concat('%',#{author},'%')")
    public List<UseBook> findByAuthor(String author) throws SQLException;

    @Select("select book_isbn, category.category_name, book_img, book_name, book_publishers, book_price, " +
            "book_author, book_introduction, book_time from book left join category on category.category_id = book.category_id " +
            "where book.category_id=#{categoryId}")
    public List<UseBook> findByCategoryId(int categoryId) throws SQLException;

    @Select("select book_isbn, category.category_name, book_img, book_name, book_publishers, book_price, " +
            "book_author, book_introduction, book_time from book left join category on category.category_id = book.category_id " +
            "where book_isbn=#{ISBN}")
    public UseBook findByISBN(String ISBN) throws SQLException;


    @Select("select book_isbn, category.category_name, book_img, book_name, book_publishers, book_price, " +
        "book_author, book_introduction, book_time from book left join category on category.category_id = book.category_id " +
        "where book_isbn=#{param} or book_name like concat('%',#{param},'%') " +
        "or book_publishers like concat('%',#{param},'%') " +
        "or book_author like concat('%',#{param},'%') " +
        "or category.category_name like concat('%',#{param},'%') " +
        "or book_introduction like concat('%',#{param},'%') " +
        "or book_time like concat('%',#{param},'%')")
    public List<UseBook> findByParam(String param) throws SQLException;
}
